package com.spedencencies.springjdbc2.repository;

import com.spedencencies.springjdbc2.model.User;
import jakarta.annotation.Resource;
import org.springframework.stereotype.Repository;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.List;
import java.util.Map;
/**
 * @author zhizhou   2024/8/26 23:31
 */
@Repository
public class UserRepository{
    
    //@Autowired
    @Resource
    private JdbcTemplate jdbcTemplate;
    
    // 添加用户
    public int addUser(User user) {
        String sql = "INSERT INTO users (name, email, age) VALUES (?, ?, ?)";
        return jdbcTemplate.update(sql, user.getName(), user.getEmail(), user.getAge());
    }
    
    // 删除用户
    public int deleteUser(int id) {
        String sql = "DELETE FROM users WHERE id = ?";
        return jdbcTemplate.update(sql, id);
    }
    
    // 更新用户
    public int updateUser(User user) {
        String sql = "UPDATE users SET name = ?, email = ?, age = ? WHERE id = ?";
        return jdbcTemplate.update(sql, user.getName(), user.getEmail(), user.getAge(), user.getId());
    }
    
    // 查询用户列表
    public List<User> getAllUsers() {
        String sql = "SELECT * FROM users";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(User.class));
    }
    
    // 使用 queryForObject 查询单个用户
    public User getUserById(int id) {
        String sql = "SELECT * FROM users WHERE id = ?";
        return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(User.class), id);
    }
    
    // 使用 queryForMap 查询用户
    public Map<String, Object> getUserAsMap(int id) {
        String sql = "SELECT * FROM users WHERE id = ?";
        return jdbcTemplate.queryForMap(sql, id);
    }
    
    // 使用 queryForList 查询多条记录
    public List<Map<String, Object>> getAllUsersAsMap() {
        String sql = "SELECT * FROM users";
        return jdbcTemplate.queryForList(sql);
    }
    
    // 使用 queryForRowSet 查询
    public void queryForRowSetExample() {
        String sql = "SELECT * FROM users";
        var rowSet = jdbcTemplate.queryForRowSet(sql);
        
        while (rowSet.next()) {
            int id = rowSet.getInt("id");
            String name = rowSet.getString("name");
            System.out.println("ID: " + id + ", Name: " + name);
        }
    }
    
    // 使用 batchUpdate 批量插入
    public int[][] batchInsertUsers(List<User> users) {
        String sql = "INSERT INTO users (name, email, age) VALUES (?, ?, ?)";
        return jdbcTemplate.batchUpdate(sql, users, users.size(), (ps, user) -> {
            ps.setString(1, user.getName());
            ps.setString(2, user.getEmail());
            ps.setInt(3, user.getAge());
        });
    }
}
